set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[csStuff_Referrers_Clean]
(
	@EarlierThan	datetime
)
AS
SET NOCOUNT ON
SET Transaction Isolation Level Read UNCOMMITTED

-- This procedure cleans out all the referrers on all posts
-- made before a given date. Based on code from:
-- http://nayyeri.net/archive/2006/04/30/CS-Dev-Guide_3A00_-How-to-reduce-database-size_3F00_-_2D00_-Part-2.aspx 

DELETE FROM cs_Referrals
WHERE UrlID IN
(
  SELECT
	UrlID
	
  FROM
	cs_Urls
	
  WHERE
	Url LIKE 'http://google.%'
    OR Url LIKE 'http://%.google.%'
	OR Url LIKE 'http://%.yahoo.%'
    OR Url LIKE 'http://yahoo.%'
	OR Url LIKE 'http://aol.%'
	OR Url LIKE 'http://%.aol.%'
	OR Url LIKE 'http://live.%'
    OR Url LIKE 'http://%search%'
    OR Url LIKE 'http://bloglines.%'
	OR Url LIKE 'http://digg.%'
	OR Url LIKE 'http://del.icio.us%'
	OR Url LIKE 'http://technorati%'
	AND LastDate <= @EarlierThan
)

DELETE FROM
	cs_Urls
	
WHERE
	UrlID NOT IN (SELECT UrlID FROM cs_Referrals)

DELETE FROM
	cs_Referrals
	
WHERE UrlID NOT IN (Select UrlID FROM cs_Urls)
	
GO



CREATE PROCEDURE [dbo].[csStuff_BannedAddresses_AddBlogSpammers]
AS
SET NOCOUNT ON
SET Transaction Isolation Level Read UNCOMMITTED
-- This procedure adds blog spammers to the Banned IP list to keep them from spamming in the future.
-- IMHO this should be a default setting in CommunityServer.
-- http://blog.danbartels.com/archive/2007/03/06/blocking-blog-comment-spammers.aspx

INSERT INTO cs_BannedAddresses
	(SettingsID,NetworkAddress,BannedDate)

SELECT
	1000,
	IPAddress,
	GETDATE()
	
FROM
	cs_posts
	
WHERE
	IsApproved = 0 AND 
	PostLevel = 2 AND 
	IPAddress NOT IN (SELECT DISTINCT NetworkAddress FROM cs_BannedAddresses WHERE SettingsID = 1000)

GROUP BY
	IPAddress HAVING Count(*) > 5

ORDER BY
	Count(*) 
